﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class MessageService
    {
        public List<Message> GetAllMessage()
        {
            List<Message> list = new List<Message>();
            string sql = "select t.MessageTypeId,t.MessageTypeName,m.BeginTime,m.Contents,m.EndTime,m.FromUserId,m.IfPublish, m.MessageId,m."
            + "RecordTime,m.Title from MessageType t,Message m where t.MessageTypeId=m.Type ";
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MessageType type = new MessageType();
                    Message msg = new Message();

                    type.MessageTypeId = (int)dr["MessageTypeId"];
                    type.MessageTypeName = dr["MessageTypeName"].ToString();
                    msg.Type = type;
                    msg.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    msg.Contents = dr["Contents"].ToString();
                    msg.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    msg.FromUserId = dr["FromUserId"].ToString();
                    msg.IfPublish = (int)dr["IfPublish"];
                    msg.MessageId = (int)dr["MessageId"];
                    msg.RecordTime = Convert.ToDateTime(dr["RecordTime"]);
                    msg.Title = dr["Title"].ToString();

                    list.Add(msg);
                }
            }
            return list;
        }
        public Message GetMessageById(int id)
        {
            Message msg = new Message();
            string sql = string.Format("select t.MessageTypeId,t.MessageTypeName,m.BeginTime,m.Contents,m.EndTime,m.FromUserId,m.IfPublish, m.MessageId,m."
            + "RecordTime,m.Title from MessageType t,Message m where t.MessageTypeId=m.Type and MessageId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MessageType type = new MessageType();
                    type.MessageTypeId = (int)dr["MessageTypeId"];
                    type.MessageTypeName = dr["MessageTypeName"].ToString();
                    msg.Type = type;
                    msg.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    msg.Contents = dr["Contents"].ToString();
                    msg.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    msg.FromUserId = dr["FromUserId"].ToString();
                    msg.IfPublish = (int)dr["IfPublish"];
                    msg.MessageId = (int)dr["MessageId"];
                    msg.RecordTime = Convert.ToDateTime(dr["RecordTime"]);
                    msg.Title = dr["Title"].ToString();

                }
            }
            return msg;
        }
        public List<Message> GetMessageByFromUserId(string fromUser)
        {
            List<Message> list = new List<Message>();
            string sql = string.Format("select t.MessageTypeId,t.MessageTypeName,m.BeginTime,m.Contents,m.EndTime,m.FromUserId,m.IfPublish, m.MessageId,m."
            + "RecordTime,m.Title from MessageType t,Message m where t.MessageTypeId=m.Type and m.FromUserId='{0}'", fromUser);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Message msg = new Message();
                    MessageType type = new MessageType();
                    type.MessageTypeId = (int)dr["MessageTypeId"];
                    type.MessageTypeName = dr["MessageTypeName"].ToString();
                    msg.Type = type;
                    msg.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    msg.Contents = dr["Contents"].ToString();
                    msg.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    msg.FromUserId = dr["FromUserId"].ToString();
                    msg.IfPublish = (int)dr["IfPublish"];
                    msg.MessageId = (int)dr["MessageId"];
                    msg.RecordTime = Convert.ToDateTime(dr["RecordTime"]);
                    msg.Title = dr["Title"].ToString();
                    list.Add(msg);
                }
            }
            return list;
        }
        public void UpdateMessage(Message msg)
        {
            string sql = "update [Message] set Title=@Title,Contents=@Contents,Type=@Type,BeginTime=@BeginTime,EndTime="
            + "@EndTime,FromUserId=@FromUserId,IfPublish=@IfPublish,RecordTime=@RecordTime where MessageId=@MessageId";
            SqlParameter[] spa = {
                                     new SqlParameter("@MessageId",msg.MessageId),
                                     new SqlParameter("@Title",msg.Title),
                                     new SqlParameter("@Contents",msg.Contents),
                                     new SqlParameter("@Type",msg.Type.MessageTypeId),
                                     new SqlParameter("@BeginTime",msg.BeginTime),
                                     new SqlParameter("@EndTime",msg.EndTime),
                                     new SqlParameter("@FromUserId",msg.FromUserId),
                                     new SqlParameter("@IfPublish",msg.IfPublish),
                                     new SqlParameter("@RecordTime",msg.RecordTime),
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void AddMessage(Message msg)
        {
            string sql = "insert [Message] (Title,Contents,Type,BeginTime,EndTime,FromUserId,IfPublish,RecordTime"
            + "values(@Title,@Contents,@Type,@BeginTime,@EndTime,@FromUserId,@IfPublish,@RecordTime)";
            SqlParameter[] spa = {
                                     new SqlParameter("@Title",msg.Title),
                                     new SqlParameter("@Contents",msg.Contents),
                                     new SqlParameter("@Type",msg.Type.MessageTypeId),
                                     new SqlParameter("@BeginTime",msg.BeginTime),
                                     new SqlParameter("@EndTime",msg.EndTime),
                                     new SqlParameter("@FromUserId",msg.FromUserId),
                                     new SqlParameter("@IfPublish",msg.IfPublish),
                                     new SqlParameter("@RecordTime",msg.RecordTime),
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public int GetCountByFromUserId(string name)
        {
            string sql = string.Format("select count(*) from Message where FromUserId='{0}'", name);
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, CommandType.Text, sql));
        }
        public void DeleteMessageByIdAndFromUserId(int id,string userId)
        {
            string sql = string.Format("delete [Message] where MessageId={0} and FromUserId='{1}'", id, userId);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }


    }
}
